DROP TABLE if EXISTS mpa, filmGenre, genres, likes, friends, users, films;

CREATE TABLE if NOT EXISTS MPA (
    mpa_id INTEGER generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    mpa_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE if NOT EXISTS films(
    film_id INTEGER generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    film_name VARCHAR(255) NOT NULL,
    film_description VARCHAR(255) NOT NULL,
    release_date DATE NOT NULL,
    duration INTEGER,
    mpa_id INTEGER
);

CREATE TABLE if NOT EXISTS users (
    user_id INTEGER generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_name VARCHAR(255),
    user_login VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    birthday DATE NOT NULL
);

CREATE UNIQUE index if NOT EXISTS email ON USERS (email);
CREATE UNIQUE index if NOT EXISTS user_login ON USERS (user_login);

CREATE TABLE if NOT EXISTS genres (
    genre_id INTEGER generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    genre_name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE if NOT EXISTS filmGenre (
PRIMARY KEY (film_id, genre_id),
    film_id INTEGER ,
    genre_id INTEGER,
    FOREIGN KEY (film_id) REFERENCES films(film_id),
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

CREATE TABLE if NOT EXISTS likes (
PRIMARY KEY (user_id,film_id),
    film_id INTEGER,
    user_id INTEGER,
    FOREIGN KEY (film_id) REFERENCES films(film_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE if NOT EXISTS friends (
    id INTEGER generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id INTEGER,
    friend_id INTEGER,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (friend_id) REFERENCES users(user_id)
);